package com.project.dao.impl;

import com.project.bean.UserBean;
import com.project.dao.UserDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class userDaoImpl extends BaseDao implements UserDao {

    /**
     * 添加
     * @param userBean 添加用户对象
     */
    @Override
    public void add(UserBean userBean) {
        this.setConnection();
        try {
            ps = con.prepareStatement(" insert into t_users(u_name,u_password,u_date,u_grade)"+"values(?,?,?,?)");
//                填充占位符
            ps.setObject(1,userBean.getName());
            ps.setObject(2,userBean.getPassword());
            ps.setObject(3,userBean.getDate());
            ps.setObject(4,userBean.getGrade());
//            更新数据库
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
//                关闭链接
           this.closeConnection();
        }
    }

    /**
     * 按id删除
     * @param id 用户id
     */
    @Override
    public void del(int id) {
        this.setConnection();
        try {
//            执行sql语句
            ps = con.prepareStatement("delete from t_users where pk_usersId=?");
//            占位
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
//            关闭
          this.closeConnection();
        }
    }

    /**
     * 按id修改密码和等级
     * @param id 用户id
     * @param password 用户密码
     * @param grade  用户等级
     */
    @Override
    public void update(int id, String password, String grade) {
        this.setConnection();
        try {

//            执行sql语句
            ps = con.prepareStatement("update t_users set u_password=? , u_grade=? where pk_usersId=?");

            ps.setObject(1,password);
            ps.setObject(2,grade);
            ps.setObject(3,id);

            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
//            关闭
            this.closeConnection();

        }
    }

    /**
     * 查询所有
     * @return
     */
    @Override
    public List<UserBean> findAll() {
        List<UserBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_users");
//            刷新
            ps.executeQuery();
//            结果集
            rs = ps.executeQuery();
//            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
            while (rs.next()){
                UserBean linkObj = this.getUser();
                list.add(linkObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
         this.closeConnection();
        }

        return list;
    }

    /**
     * 按id查询用户
     * @param id 用户id
     * @return
     */
    @Override
    public UserBean findById(int id) {
        UserBean linkObj = new UserBean();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_users where pk_usersId=?");
            ps.setObject(1,id);
//            刷新
            ps.executeQuery();

//            结果集
            rs = ps.executeQuery();

//            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
            while (rs.next()) {
               linkObj=this.getUser();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();
        }

        return linkObj;
    }

    /**
     * 按名字模糊查询
     * @param name 用户姓名
     * @return
     */
    @Override
    public List<UserBean> findByName(String name) {
        List<UserBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_users where u_name like ?");
            String s = "%"+name+"%";
            ps.setObject(1,s);
//            刷新
            ps.executeQuery();

//            结果集
            rs = ps.executeQuery();
//            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
            while (rs.next()){
                UserBean linkObj = this.getUser();
                list.add(linkObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
          this.closeConnection();
        }

        return list;
    }

    /**
     * 按生日起到查用户
     * @param date 用户生日
     * @return
     */
    @Override
    public List<UserBean> findByDate(String date ,String date1) {
        List<UserBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_users where u_date<? and u_date>?");
            ps.setObject(1,date);
            ps.setObject(2,date1);
//            刷新
            ps.executeQuery();

//            结果集
            rs = ps.executeQuery();
//            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
            while (rs.next()){
                UserBean linkObj =this.getUser();
                list.add(linkObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();
        }
        return list;
    }

    /**
     * 按等级查用户
     * @param Grade 用户等级
     * @return
     */
    @Override
    public List<UserBean> findByGrade(String Grade) {
        List<UserBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_users where u_grade=?");
            ps.setObject(1,Grade);
//            刷新
            ps.executeQuery();
//            结果集
            rs = ps.executeQuery();
//            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
            while (rs.next()){
                UserBean linkObj = this.getUser();
                list.add(linkObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
          this.closeConnection();
        }
        return list;
    }
    /**
     * 按登陆查询
     * @param name
     * @param password
     * @return
     */
    @Override
    public UserBean finByLang(String name, String password) {
        UserBean linkObj = new UserBean();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_users where u_name=? and u_password=? ");
            ps.setObject(1,name);
            ps.setObject(2,password);
//            结果集
            rs = ps.executeQuery();
            while (rs.next()){
            linkObj=this.getUser();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
         this.closeConnection();
        }
        return linkObj;
    }

    @Override
    public List<UserBean> findByItem(String name, LocalDate startDate, LocalDate endDate) {
        List<UserBean> list = new ArrayList<>();
        List list1 = new ArrayList<>();
        this.setConnection();
        try {

                String s = "select * from t_users where 1=1 ";
                if (name!=null&&name.length()!=0){
                    s+="and u_name like ? ";
                    list1.add("%"+name+"%");
                }
                if (startDate!=null){
                    s+="and u_date>=? ";
                    list1.add(startDate);
                }
                if (endDate!=null){
                    s+="and u_date>=? ";
                    list1.add(endDate);
                }
                ps=con.prepareStatement(s);
//                ps.setObject(1,name);
//                ps.setObject(2,startDate);
//                ps.setObject(3,endDate);


            for (int i = 0; i < list1.size(); i++) {
                ps.setObject(i+1,list1.get(i));
            }
            //            结果集
            rs = ps.executeQuery();
//            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
            while (rs.next()){
                UserBean linkObj = this.getUser();
                list.add(linkObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return list;
    }


    private UserBean getUser() throws SQLException{
        //            将结果集指针指向下一天记录，返回真，如果没有返回假，遍历结束。
        UserBean linkObj = new UserBean();
                linkObj.setId(rs.getInt("pk_usersId"));
                linkObj.setName(rs.getString("u_name"));
                linkObj.setPassword(rs.getString("u_password"));
                linkObj.setDate(LocalDate.parse(rs.getString("u_date")));
                linkObj.setGrade(rs.getString("u_grade"));
        return linkObj ;
    };


    public static void main(String[] args) {
        UserDao dao = new userDaoImpl();
//        dao.add(new UserBean("张五","987654", LocalDate.parse("1997-05-03"),"超级用户"));
//        dao.del(5);
//        dao.update(1,"234567","超级用户");
//        System.out.println(dao.findAll());
//        System.out.println(dao.findById(2));
//        System.out.println(dao.findByName("张"));
//        System.out.println(dao.findByDate("1998-07-11","1988-03-06"));
//        System.out.println(dao.findByGrade("超级用户"));
//        System.out.println(dao.finByLang("李四","234567"));
        System.out.println(dao.findByItem("赵",LocalDate.parse("1997-05-03"),LocalDate.parse("1998-05-03")));
    }
}
